const express = require('express');
const { executeQuery, handleError, formatDateTime } = require('../utils');
const router = express.Router();

// 新增借阅记录
router.post('/add', async (req, res) => {
    const { user_id, journal_id, borrow_date, return_date } = req.body;

    const formattedBorrowDate = formatDateTime(borrow_date);
    const formattedReturnDate = formatDateTime(return_date);

    // 设置锁定期为两天后
    const lockExpiration = new Date();
    lockExpiration.setDate(lockExpiration.getDate() + 2);
    const formattedLockExpiration = formatDateTime(lockExpiration);

    const addBorrowQuery = `
        INSERT INTO borrowrecords (user_id, journal_id, borrow_date, return_date, lock_expiration, status)
        VALUES (?, ?, ?, ?, ?, 'locked');
    `;

    const updateStockQuery = `
        UPDATE journals
        SET stock = stock - 1
        WHERE journal_id = ? AND stock > 0;
    `;

    // 查询用户当前借阅的数量（状态为 locked 或 borrowed）
    const checkBorrowedOrLockedQuery = `
        SELECT COUNT(*) as current_borrowed
        FROM borrowrecords
        WHERE user_id = ? AND (status = 'locked' OR status = 'borrowed');
    `;

    try {
        // 获取用户当前借阅中（包括锁定期）期刊的数量
        const [borrowedOrLockedResult] = await executeQuery(checkBorrowedOrLockedQuery, [user_id]);
        const currentBorrowedOrLocked = borrowedOrLockedResult.current_borrowed;

        // 查询用户的借阅上限
        const checkUserLimitQuery = `
            SELECT borrow_limit
            FROM users
            WHERE user_id = ?;
        `;

        const [userLimitResult] = await executeQuery(checkUserLimitQuery, [user_id]);
        const borrowLimit = userLimitResult.borrow_limit;

        // 判断用户是否已经借够了
        if (currentBorrowedOrLocked >= borrowLimit) {
            return res.status(400).json({ message: '您已经达到借阅上限，无法借阅更多期刊' });
        }

        // 更新库存
        const stockResult = await executeQuery(updateStockQuery, [journal_id]);
        if (stockResult.affectedRows === 0) {
            return res.status(400).json({ message: '库存不足，无法借阅' });
        }

        // 添加借阅记录
        await executeQuery(addBorrowQuery, [user_id, journal_id, formattedBorrowDate, formattedReturnDate, formattedLockExpiration]);

        res.json({ message: '借阅记录添加成功，进入锁定期' });
    } catch (error) {
        handleError(error, res);
    }
});

// 查询借阅记录
router.post('/list', async (req, res) => {
    const { page = 1, pageSize = 10, user_id } = req.body;  // 接收 user_id 参数

    const offset = (page - 1) * pageSize;
    const limit = pageSize;

    // 判断是否传入 user_id 参数，如果有则按 user_id 过滤
    const totalQuery = user_id ? `
        SELECT COUNT(*) AS total
        FROM borrowrecords
        WHERE user_id = ?;
    ` : `
        SELECT COUNT(*) AS total
        FROM borrowrecords;
    `;

    const query = user_id ? `
        SELECT
            borrow_id,
            u.user_id AS user_id,
            u.name AS username,
            jd.journal_name AS journal_name,
            j.year AS year,
            j.volume AS volume,
            j.issue AS issue,
            br.borrow_date AS borrow_date,
            br.return_date AS return_date,
            br.status AS status
        FROM 
            borrowrecords br
        JOIN 
            users u ON br.user_id = u.user_id
        JOIN 
            journals j ON br.journal_id = j.journal_id
        JOIN 
            journaldirectories jd ON j.directory_id = jd.directory_id
        WHERE
            br.user_id = ?
        LIMIT ${limit} OFFSET ${offset};
    ` : `
        SELECT
            borrow_id,
            u.user_id AS user_id,
            u.name AS username,
            jd.journal_name AS journal_name,
            j.year AS year,
            j.volume AS volume,
            j.issue AS issue,
            br.borrow_date AS borrow_date,
            br.return_date AS return_date,
            br.status AS status
        FROM 
            borrowrecords br
        JOIN 
            users u ON br.user_id = u.user_id
        JOIN 
            journals j ON br.journal_id = j.journal_id
        JOIN 
            journaldirectories jd ON j.directory_id = jd.directory_id
        LIMIT ${limit} OFFSET ${offset};
    `;

    try {
        // 执行总记录数查询
        const totalResult = await executeQuery(totalQuery, user_id ? [user_id] : []);
        const total = totalResult[0]?.total || 0;

        // 执行借阅记录查询
        const records = await executeQuery(query, user_id ? [user_id] : []);

        // 格式化日期字段
        const formattedRecords = records.map(record => ({
            ...record,
            borrow_date: formatDateTime(record.borrow_date),
            return_date: formatDateTime(record.return_date),
        }));

        // 返回数据
        res.json({ total, page, pageSize, data: formattedRecords });
    } catch (error) {
        console.error('查询借阅记录出错:', error);
        handleError(error, res);
    }
});

// 删除借阅记录
router.post('/delete', async (req, res) => {
    const { borrow_id } = req.body;

    const query = 'DELETE FROM borrowrecords WHERE borrow_id = ?;';

    try {
        await executeQuery(query, [borrow_id]);
        res.json({ message: '借阅记录删除成功' });
    } catch (error) {
        handleError(error, res);
    }
});

// 修改借阅状态
router.post('/update-status', async (req, res) => {
    const { borrow_id, status } = req.body;

    const checkFineQuery = `
        SELECT COUNT(*) AS unpaid_fines
        FROM fines
        WHERE borrow_id = ? AND status = 'unpaid';
    `;

    const updateBorrowQuery = `
        UPDATE borrowrecords
        SET status = ?
        WHERE borrow_id = ?;
    `;

    const updateBorrowTimeQuery = `
        UPDATE borrowrecords
        SET borrow_date = NOW()
        WHERE borrow_id = ?;
    `;

    const updateStockQuery = `
        UPDATE journals
        SET stock = stock + 1
        WHERE journal_id = (
            SELECT journal_id FROM borrowrecords WHERE borrow_id = ?
        );
    `;

    try {
        const fineResult = await executeQuery(checkFineQuery, [borrow_id]);
        const unpaidFines = fineResult[0]?.unpaid_fines || 0;

        if (unpaidFines > 0) {
            return res.status(400).json({
                message: '借阅记录存在未支付的罚款，请先前往罚款页面缴纳罚款。',
            });
        }
        if (status === 'cancelled' || status === 'returned') {
            await executeQuery(updateStockQuery, [borrow_id]);
        }
        if (status == 'borrowed') {
            await executeQuery(updateBorrowTimeQuery,[borrow_id])
        }
        await executeQuery(updateBorrowQuery, [status, borrow_id]);
    
        res.json({ message: '借阅状态更新成功' });
    } catch (error) {
        handleError(error, res);
    }
});

module.exports = router;
